﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;

namespace DataAccess
{
    public static class DataProvider
    {
        private static readonly string dataProvider =  ConfigurationManager.AppSettings.Get("DataProvider");
        private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
        private static readonly string connectionString = ConfigurationManager.AppSettings.Get(dataProvider);

        public static int Update(string sql, params DbParameter[] param)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    if (param != null)
                        command.Parameters.AddRange(param);

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }
        public static int Insert(string sql, bool getId, params DbParameter[] param)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    if (param != null)
                        command.Parameters.AddRange(param);

                    connection.Open();
                    command.ExecuteNonQuery();

                    int id = -1;

                    // Check if new identity is needed.
                    if (getId)
                    {
                        // Execute db specific autonumber or identity retrieval code
                        // SELECT SCOPE_IDENTITY() -- for SQL Server
                        // SELECT @@IDENTITY -- for MS Access
                        // SELECT MySequence.NEXTVAL FROM DUAL -- for Oracle
                        string identitySelect;
                        switch (dataProvider)
                        {
                            // Access
                            case "System.Data.OleDb":
                                identitySelect = "SELECT @@IDENTITY";
                                break;
                            // Sql Server
                            case "System.Data.SqlClient":
                                identitySelect = "SELECT @@IDENTITY";
                                break;
                            // Oracle
                                /*
                            case "System.Data.OracleClient":
                                identitySelect = "SELECT MySequence.NEXTVAL FROM DUAL";
                                break;
                                 */
                            default:
                                identitySelect = "SELECT SCOPE_IDENTITY()";
                                break;
                        }
                        command.CommandText = identitySelect;
                        command.Parameters.Clear();
                        object obj = command.ExecuteScalar();
                        if (obj != null)
                            id = int.Parse(obj.ToString());
                    }
                    return id;
                }
            }

        }
        public static void Insert(string sql, params DbParameter[] param)
        {
            Insert(sql, false, param);
        }

        public static DataSet GetDataSet(string sql, params DbParameter[] param)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;
                    if (param != null)
                        command.Parameters.AddRange(param);

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;

                        DataSet ds = new DataSet();
                        adapter.Fill(ds);

                        return ds;
                    }
                }
            }
        }
        public static DataTable GetDataTable(string sql, params DbParameter[] param)
        {
            return GetDataSet(sql, param).Tables[0];
        }
        public static DataRow GetDataRow(string sql, params DbParameter[] param)
        {
            DataRow row = null;

            DataTable dt = GetDataTable(sql, param);
            if (dt.Rows.Count > 0)
            {
                row = dt.Rows[0];
            }

            return row;
        }
        public static object GetObject(string sql, params DbParameter[] param)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    if (param != null)
                        command.Parameters.AddRange(param);

                    connection.Open();
                    return command.ExecuteScalar();
                }
            }
        }

    }
}
